package com.dotflat.xj.repository;

import com.dotflat.xj.domain.Target;
import com.dotflat.xj.domain.TargetRecord;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TargetRecordRepository extends JpaRepository<TargetRecord, String>, JpaSpecificationExecutor<TargetRecord> {

    @Query(value="SELECT * FROM xj_target_record  where targetId = ?2 and  areaId = ?1 and targetClass = ?4 and  DATE_FORMAT(created,'%Y-%m-%d') = DATE_FORMAT(?3,'%Y-%m-%d') and createdBy = ?4",nativeQuery = true)
    TargetRecord selectTargetRecord(String areaId,String targetId,String created,String targetClass,String staffId);


    @Query(value=" SELECT tr.id, tr.created, tr.deleted, tr.areaId, tr.audited, " +
            "tr.createdBy, tr.targetId, tr.status, tr.type, tr.recordCount,  " +
            "tr.recordNumber, tr.time, IFNULL(tc.name,'') targetClass, tr.userName FROM xj_target_record tr  LEFT JOIN xj_target_class tc on tc.id = tr.targetClass " +
            " where tr.targetId = ?2 and  tr.areaId = ?1 and  DATE_FORMAT(tr.created,'%Y-%m-%d') = DATE_FORMAT(?3,'%Y-%m-%d')",nativeQuery = true)
    List<TargetRecord> getList(String areaId, String targetId, String created);

    TargetRecord findAllById(String id);

    @Query(value = "SELECT ifnull(SUM(tr.recordCount),0)  recordCount  FROM  xj_target_record  tr  INNER JOIN xj_target t on t.id = tr.targetId  " +
            " WHERE  t.status= 0  and tr.areaId  = :areaId    AND  DATE_FORMAT(tr.created,'%Y-%m-%d') = DATE_FORMAT(:created,'%Y-%m-%d') AND if(:sort != '', t.sort = :sort, 1=1) ",nativeQuery = true)
    Integer   targetCountSum(@Param("areaId")String areaId, @Param("created")String created,@Param("sort") String sort);

    @Query(value = "SELECT ifnull(SUM(tr.recordNumber),0)  recordNumber  FROM  xj_target_record  tr INNER JOIN xj_target t on t.id = tr.targetId " +
            " WHERE  t.status= 0  and tr.areaId  = :areaId   AND  DATE_FORMAT(tr.created,'%Y-%m-%d') = DATE_FORMAT( :created,'%Y-%m-%d') AND if(:sort != '', t.sort = :sort, 1=1)  ",nativeQuery = true)
    Integer   targetNumberSum(@Param("areaId")String areaId, @Param("created")String created,@Param("sort") String sort);


    @Query(value = "SELECT ifnull(SUM(tr.recordCount),0)  recordCount  FROM  xj_target_record  tr  INNER JOIN xj_target t on t.id = tr.targetId " +
            " WHERE  t.status= 0  and tr.areaId  = :areaId     AND  DATE_FORMAT(tr.created,'%Y-%m-%d  %H:%i:%S') >= DATE_FORMAT(:startTime,'%Y-%m-%d  %H:%i:%S')  AND  DATE_FORMAT(tr.created,'%Y-%m-%d  %H:%i:%S') <= DATE_FORMAT(:endTime,'%Y-%m-%d  %H:%i:%S')  AND if(:sort != '', t.sort = :sort, 1=1) ",nativeQuery = true)
    Integer   weekCountSum(@Param("areaId")String areaId, @Param("startTime")String startTime, @Param("endTime")String endTime, @Param("sort")String sort);

    @Query(value = "SELECT ifnull(SUM(tr.recordNumber),0)  recordNumber  FROM  xj_target_record  tr INNER JOIN xj_target t on t.id = tr.targetId  " +
            " WHERE t.status= 0  and tr.areaId  = :areaId     AND  DATE_FORMAT(tr.created,'%Y-%m-%d  %H:%i:%S') >= DATE_FORMAT(:startTime,'%Y-%m-%d  %H:%i:%S')  AND  DATE_FORMAT(tr.created,'%Y-%m-%d  %H:%i:%S') <= DATE_FORMAT(:endTime,'%Y-%m-%d  %H:%i:%S')  AND if(:sort != '', t.sort = :sort, 1=1)  ",nativeQuery = true)
    Integer   weekNumberSum(@Param("areaId")String areaId, @Param("startTime")String startTime, @Param("endTime")String endTime, @Param("sort")String sort);

    @Query(value = "SELECT tr.*  FROM  xj_target_record  tr INNER JOIN xj_target t on t.id = tr.targetId  " +
            " WHERE t.status= 0  and tr.areaId  = :areaId   AND  DATE_FORMAT(tr.created,'%Y-%m-%d  %H:%i:%S') >= DATE_FORMAT(:startTime,'%Y-%m-%d  %H:%i:%S')  AND  DATE_FORMAT(tr.created,'%Y-%m-%d  %H:%i:%S') <= DATE_FORMAT(:endTime,'%Y-%m-%d  %H:%i:%S')  AND if(:sort != '', t.sort = :sort, 1=1)  ",nativeQuery = true)
    List<TargetRecord> getAreaList(@Param("areaId")String areaId, @Param("startTime")String startTime, @Param("endTime")String endTime, @Param("sort")String sort);

}
